package Management;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class TeacherExcel {
    /**
     * 开始excle操作
     * 获取工作簿
     * 获得第一个sheet页
     * 获取单元格权限
     * 链接
     * @param file
     * @return
     */
    public ArrayList<TeacherPojo> readSTeacherInfo(File file) {
        {
            // 开始excel操作
            ArrayList<TeacherPojo> slist = new ArrayList<>();
            // 工作薄
            XSSFWorkbook wb = null;
            try {
                wb = new XSSFWorkbook(file);
                // 获取第一个sheet页
                XSSFSheet sheet1 = wb.getSheetAt(0);

                for (int i = 1; i <= sheet1.getLastRowNum(); i++) {
                    // 获取单元格
                    XSSFRow row = sheet1.getRow(i);
                    XSSFCell cell1 = row.getCell(0);
                    //获取的第一单元格
                    XSSFCell cell2 = row.getCell(1);
                    //获取的第二单元格
                    XSSFCell cell3 = row.getCell(2);
                    //获取的第三单元格
                    XSSFCell cell4 = row.getCell(3);
                    //获取的第四单元格
                    /*XSSFCell cell5 = row.getCell(4);
                    XSSFCell cell6 = row.getCell(5);*/
//                                                          System.out.println(cell1.getStringCellValue());
//                                                            System.out.println(cell2.getStringCellValue());
                    TeacherPojo pojo = new TeacherPojo();
                    //更新教师表
                    pojo.setTno(ExcelUtil.getStringCellValue(cell1));
                    //更新cell1
                    pojo.setTname(cell2.getStringCellValue().toString());
                    //更新cell2


//

 //                   pojo.setSdate(ExcelUtil.getStringCellValue(cell3));
                    pojo.setTsex(cell3.getStringCellValue().toString());

 //                   cell5.setCellType(CellType.STRING);
//                    System.out.println(ExcelUtil.getStringCellValue(cell5));

 //                   pojo.setSgrade(Integer.parseInt(cell5.getStringCellValue().toString()));
                    pojo.setCno(ExcelUtil.getStringCellValue(cell4));
                    slist.add(pojo);
                    //添加pojo
                }

                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }

            return slist;
            //结束 slist
        }
    }

    public void exportto(String filepath, List<TeacherPojo> slist) throws IOException {
        /**
         * 进入工作簿
         * 获取第一个sheet页
         * 组装表头
         * 表头分别是教师号，姓名，性别，课程号
         * 组装表主体
         * 获取单元格
         * 文件和内存wb绑定
         */
        File file = new File(filepath);
        //更新File(filepath)
        FileOutputStream out = new FileOutputStream(file);
        // 工作薄
        XSSFWorkbook wb = null;
        //定义属性
        wb = new XSSFWorkbook();
        // 获取第一个sheet页
        XSSFSheet sheet1 = wb.createSheet("教师表");

        // 组装表头
        XSSFRow row = sheet1.createRow(0);
        XSSFCell cell1 = row.createCell(0);
        //填充第一列
        XSSFCell cell2 = row.createCell(1);
        //填充第二列
        XSSFCell cell3 = row.createCell(2);
        //填充第三列
        XSSFCell cell4 = row.createCell(3);
        //填充第三列
       /* XSSFCell cell5 = row.createCell(4);
        XSSFCell cell6 = row.createCell(5);
        XSSFCell cell7 = row.createCell(6);*/
        cell1.setCellValue("教师号");
        //第一列为教师号
        cell2.setCellValue("姓名");
        //第二列为姓名
        cell3.setCellValue("性别");
        //第三列为性别
        cell4.setCellValue("课程号");
        //第四列为课程号
       /* cell5.setCellValue("成绩");
        cell6.setCellValue("专业");
        cell7.setCellValue("班号");*/
        // 组装表主体
        for (int i = 0; i < slist.size(); i++) {
            //进入for循环
            TeacherPojo item = slist.get(i);

            XSSFRow newrow = sheet1.createRow(i + 1);
            XSSFCell newcell1 = newrow.createCell(0);
            // 获取单元格1
            XSSFCell newcell2 = newrow.createCell(1);
            // 获取单元格2
            XSSFCell newcell3 = newrow.createCell(2);
            // 获取单元格3
            XSSFCell newcell4 = newrow.createCell(3);
            // 获取单元格4
            /*XSSFCell newcell5 = newrow.createCell(4);
            XSSFCell newcell6 = newrow.createCell(5);
            XSSFCell newcell7 = newrow.createCell(6);*/
            newcell1.setCellValue(item.getTno());
            //更新Tno列
            newcell2.setCellValue(item.getTname());
            //更新Tname列
            newcell3.setCellValue(item.getTsex());
            //更新getTsex列
            newcell4.setCellValue(item.getCno());
            //更新getCno列
            /*newcell5.setCellValue(item.getSgrade());
            newcell6.setCellValue(item.getCareer());
            newcell7.setCellValue(item.getCid());*/
        }
        // 获取单元格


        wb.write(out);
        // 文件和  内存wb绑定
        out.close();
        //结束out
        wb.close();
        //结束wb
    }
    }
